<?php
/**
 * This file is part of the Agora distribution. 
 * Detailed copyright and licensing information can be found
 * in the gpl-3.0.txt file which should be included in the distribution.
 * 
 * @version		$Id: 3.0.14 Olympus 2010-09-28 rammstein4o $
 * @copyright  2007 - 2010 jVitals
 * @license   GPLv3 Open Source
 * @link       http://jvitals.com
 * @since      File available since initial release
 */
defined ('IN_AGORA') or die;

class UserModel extends Model
{
    function __construct()
    {
        parent::__construct('users');
    }

    function deleteList($ids)
    {
        $this->db->bindList('ids',$ids,'integer',true);

        $this->db->setQuery('DELETE FROM ##__read_posts WHERE user_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('UPDATE ##__posts SET poster=\'Guest\', poster_id=0 WHERE poster_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__profile_field_entries WHERE u_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__messages WHERE sender_id IN (:ids) OR owner IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__ratings WHERE user_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__reputation WHERE user_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__subscriptions WHERE user_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__user_group WHERE user_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__user_warning WHERE user_id IN (:ids)');
        $this->db->query();

        $this->db->setQuery('DELETE FROM ##__users WHERE id IN (:ids)');
        $this->db->query();

        $this->db->clear();
    }

    function createUserFromJoomla(& $my)
    {
        $user_id = $this->add(array(
                    'jos_id' => $my->id,
                    'last_visit' => intval(strtotime($my->lastvisitDate)),
                    'registered' => intval(strtotime($my->registerDate)),
                    'username' => $my->username,
                    'email' => $my->email,
            ));

        $access_model = & Model::getInstance('AccessModel');

        if ($my->usertype == 'Super Administrator' || $my->usertype == 'Administrator') {
            $role = AGORA_ROLE_ADMIN;
        } else {
            $role = AGORA_ROLE_MEMBER;
        }

        // Place user in global group with role based on Joomla access level
        $access_model->add(array(
                    'user_id' => $user_id,
                    'role_id' => $role,
                    'group_id' => 1,
            ));
    }

    function loadGuest($username = null, $email = null)
    {
    	if (is_null($username) || empty($username)) {
    		$username = 'Guest';
    	}
/*			$user = $this->load(1);
            if (!$user) {
                print "cannot load guest"; die;
            }*/
        $user = array();
        $user['id'] = 0;
        $user['username'] = $username;
        $user['disp_posts'] = 0;
        $user['disp_topics'] = 0;
        $user['reverse_posts'] = 0;
        $user['show_smilies'] = 1;
        $user['show_sig'] = 0;
        $user['show_img_sig'] = 0;
        $user['show_img'] = 1;
        $user['is_superadmin'] = 0;
        $user['url'] = '';
        $user['auto_subscriptions'] = 1;
	    	if (!(is_null($email) || empty($email))) {
	    		$user['email'] = $email;
	    	}
        return $user;
    }

    function loadCurrent()
    {
        $my = & JFactory::getUser();
        if ($my->id) {
            $user = $this->load($my->id,'jos_id');
            if (!$user) {
                $this->createUserFromJoomla($my);
                $user = $this->load($my->id,'jos_id');
            } else {
				$data = array();
                $data['last_visit'] = strtotime($my->lastvisitDate);
                if ($user['email'] != $my->email) {
                    $data['email'] = $my->email;
                }
                if ($user['username'] != $my->username ) {
                    $data['username'] = $my->username;
                }
                if ($user['last_known_ip'] != Agora::getIP()) {
                    $data['last_known_ip'] = Agora::getIP();
                }
                // Update loaded user to not load it again
                foreach ($data as $param=>$value) {
                    $user[$param] = $value;
                }
                $this->edit($user['id'],$data);

            }
            if ($my->usertype == 'Super Administrator' || $my->usertype == 'Administrator') {
                $user['is_superadmin'] = 1;
            } else {
                $user['is_superadmin'] = 0;
            }
			$user['rss_id'] = md5($my->username.$my->password);
            return $user;
        } else {
			$user['rss_id'] = '';
            return $this->loadGuest();
        }
    }

	function loadAllCount() {
		$this->db->setQuery('SELECT COUNT(*) FROM '.$this->table.' as u LEFT JOIN #__users AS j_u ON u.jos_id = j_u.id '.$this->_filter);
		return $this->db->loadResult();
	}

	function loadAll($key = '', $per_page = null, $page = null) {
		if (is_null($per_page) || is_null($page)) {
			$limit = '';
		} else {
			$limit = Model::getLimit($per_page, $page);
		}

		$this->db->setQuery('SELECT u.*, j_u.name FROM '.$this->table .' as u LEFT JOIN #__users AS j_u ON u.jos_id = j_u.id '.$this->_filter.' '. $this->order .' '. $limit);
		$result =  $this->db->loadAssocList($key);
		return $result;
	}    
    
    function load($value, $field = 'id') {

		if (is_array($value)) {
	        $this->db->bindList('field_value',$value,'string');
			$where = 'u.'.$field.' IN (:field_value)';
		} else {
	        $this->db->bind('field_value',$value,'string');
			$where = 'u.'.$field.' = :field_value';
		}

        $sql = 'SELECT u.*, j_u.name, session.userid IS NOT NULL as online'.
                ' FROM ##__users AS u '.
                ' LEFT JOIN #__session AS session'.
                    ' ON u.jos_id = session.userid'.
                ' LEFT JOIN #__users AS j_u'.
                    ' ON u.jos_id = j_u.id'.
        
                ' WHERE '.$where;
        $this->setQuery($sql);
		
		if (is_array($value)) {
			return $this->db->loadAssocList($field);
		} else {
			return $this->db->loadAssoc();
		}
    }
    
    function getJUser($id)
    {
        $this->setQuery('SELECT *'.
                            ' FROM #__users'.
                            ' WHERE id = '.intval($id));
        return $this->db->loadAssoc();
    }    

    function getGroups($id)
    {
        $this->setQuery('SELECT grp.id AS id, grp.parent_id, grp.name AS name, role.name AS role, ug.jooplicense_id AS lic_id'.
                            ' FROM ##__user_group AS ug'.
                            ' INNER JOIN ##__group AS grp'.
                            '  ON ug.group_id = grp.id'.
                            ' INNER JOIN ##__roles AS role'.
                            '  ON ug.role_id = role.id'.
                            ' WHERE ug.user_id = '.intval($id));
        return $this->db->loadAssocList();
    }

    function getGroupsCount($id)
    {
        $this->setQuery('SELECT COUNT(*) FROM ##__user_group WHERE user_id = '.intval($id));
        return $this->db->loadResult();
    }

    function getTop($limit)
    {
        $sql = 'SELECT u.id, u.username, j_u.name'.
                ' FROM ##__users as u'.
                ' LEFT JOIN #__users AS j_u'.
                    ' ON u.jos_id = j_u.id'.
                ' WHERE u.num_posts > 0'.
                ' ORDER BY u.num_posts DESC'.
                ' LIMIT '.intval($limit);
        $this->setQuery($sql);
		
        return $this->db->loadAssocList();
    }

    function searchUsersCount($username, $group)
    {
        $where = array();
        if (!empty($username)) {
            $this->db->bind('username',str_replace('*','%',$username),'string');
            $where[] = 'username LIKE :username';
        }

        $filter_group = '';
        if ($group > 0) {
            $filter_group = ' INNER JOIN ##__user_group AS ug'.
                                ' ON u.id = ug.user_id ';

            $this->db->bind('group',$group,'integer');
            $where[] = 'ug.group_id = :group';
        }

        if (!empty($where)) {
            $where = 'WHERE '.implode(' AND ',$where);
        } else {
            $where = '';
        }

        $this->db->setQuery('SELECT COUNT(*)'.
                        ' FROM ##__users AS u'.
            $filter_group.
                        " $where");
        return $this->db->loadResult();
    }

    function searchUsers($username, $group, $sort_by, $sort_dir)
    {
        $where = array();
        if (!empty($username)) {
            $config = & Model::getInstance('ConfigModel');
			$agora_config = $config->load();
            
            $this->db->bind('username',str_replace('*','%',$username),'string');
            if ($agora_config['o_fullnames_enabled'])
            $where[] = 'ju.name LIKE :username';
            else
            $where[] = 'u.username LIKE :username';
        }

        $filter_group = '';
//        $sort_by = 'u.'.$sort_by;
        if ($group > 0) {
            $filter_group = ' INNER JOIN ##__user_group AS ug'.
                                ' ON u.id = ug.user_id';

            $this->db->bind('group',$group,'integer');
            $where[] = 'ug.group_id = :group';
        }

        if (!empty($where)) {
            $where = 'WHERE '.implode(' AND ',$where);
        } else {
            $where = '';
        }

        $this->db->setQuery('SELECT u.id, ju.name, u.username, u.title, u.num_posts, u.use_avatar, u.registered'.
                        ' FROM ##__users AS u LEFT JOIN #__users AS ju ON u.jos_id=ju.id'.
            $filter_group.
                        " $where ORDER BY $sort_by $sort_dir");

        return $this->db->loadAssocList();
    }

    function getUserId($username, $full_name = false) //**Another try to fix for both real names and user-names
    {
		$username = $this->db->Quote(trim($username));
		if ($full_name ) {
			$where = 'LOWER(TRIM(j.name)) = LOWER('. $username .')';
		}
		else {
			$where = 'LOWER(TRIM(j.username)) = LOWER('. $username .')';
		}
        //// We need it in PM // if we need real name from Joomla we will return to JOIN in future
 			 $this->db->setQuery("SELECT u.id".
                " FROM {$this->table} AS u".
                " INNER JOIN #__users AS j".
                "  ON u.jos_id = j.id".
                " WHERE ". $where);
       //$this->db->bind('username',trim($username),'string');
        //$this->db->setQuery('SELECT id FROM #__users WHERE '. $where ); 
		//$this->db->setQuery($query );
		return $this->db->loadResult();
        
    }

    function getUserEmail($username, $full_name = false) //**Another try to fix for both real names and user-names
    {
		$username = $this->db->Quote(trim($username));
		if ($full_name ) {
			$where = 'LOWER(TRIM(j.name)) = LOWER('. $username .')';
		}
		else {
			$where = 'LOWER(TRIM(j.username)) = LOWER('. $username .')';
		}
        //// We need it in PM // if we need real name from Joomla we will return to JOIN in future
/*			$this->db->setQuery("SELECT id".
                " FROM {$this->table} AS u".
                " INNER JOIN #__users AS j".
                "  ON u.jos_id = j.id".
                " WHERE LOWER(TRIM(j.name)) = LOWER({$this->db->Quote(trim($username))})");*/
        //$this->db->bind('username',trim($username),'string');
        //$this->db->setQuery('SELECT email FROM ##__users WHERE LOWER(TRIM(username)) = LOWER(:username)');
		
		 $this->db->setQuery("SELECT j.email".
			" FROM #__users AS j".
			" WHERE ". $where);
        return $this->db->loadResult();
    }

    function getUserFullName($username)
    {
        //// We need it in PM // if we need real name from Joomla we will return to JOIN in future
        $this->db->setQuery("SELECT name".
                " FROM {$this->table} AS u".
                " INNER JOIN (
					SELECT * FROM #__users WHERE LOWER(TRIM(username)) = LOWER({$this->db->Quote(trim($username))}) ) AS j".
                "  ON u.jos_id = j.id");
        return $this->db->loadResult();
    }

    function moderateAny($user_id)
    {
        $this->db->bind('user_id',$user_id,'integer');
        $this->db->bindList('roles',array(AGORA_ROLE_ADMIN,AGORA_ROLE_MODERATOR),'integer');
        $this->db->setQuery('SELECT COUNT(*) FROM ##__user_group WHERE user_id=:user_id AND role_id IN (:roles)');
        return $this->db->loadResult() > 0 ? true : false;
    }

	function loadByKey($key)
	{
        $this->db->bind('key',$key,'string');
        $this->setQuery('SELECT id FROM #__users WHERE MD5(CONCAT(username,password)) = :key');
        $id = $this->db->loadResult();
		return $this->load($id,'jos_id');
	}
	
	/*
	function sendNewPasswrds($count)
	{
	    $mail = JFactory::getMailer();
        $this->setQuery('SELECT pswrd FROM #__tmp_users_psw WHERE email =\'last_time\'');
        $l_time = $this->db->loadResult();
        if ($l_time)
        {
            if ( time() - $l_time > 3600)
            {
                $this->setQuery('SELECT * FROM #__tmp_users_psw LIMIT '.$count);
                $emails = $this->db->loadAssocList();
                if ($emails)
                {
                    foreach ($emails as $email)
                    {
                        $mail->addRecipient( $email['email']);
                //		$mail->setSender( array( $email, $name ) );
                        $mail->setSubject( 'Agora forum: New password');
                        $body = 'After user convertation from phpbb3 to agora your new password is '.$email['pswrd'];
                        $mail->setBody( $body );
                        $mail->IsHTML(true);
                        $sending = $mail->Send();
                        if (!isset($sending->code))
                        {
                            $this->setQuery('DELETE FROM #__tmp_users_psw WHERE `email`=\''.$email['email'].'\'');
                            $this->db->query();
                        }
                    }
                    $this->setQuery('UPDATE #__tmp_users_psw SET pswrd='.time().' WHERE email=\'last_time\'');
                    $this->db->query();
                }
                else
                {
                    $this->setQuery('DROP TABLE IF EXISTS `#__tmp_users_psw`');
                    $this->db->query();
                    $this->setQuery('DELETE FROM #__agora_config WHERE conf_name =\'o_emailpsw_perhour\'');
                    $this->db->query();
                }
            }
        }
        else
        {
                $this->setQuery('SELECT * FROM #__tmp_users_psw LIMIT '.$count);
                $emails = $this->db->loadAssocList();
                if ($emails)
                {
                    foreach ($emails as $email)
                    {
                        $mail->addRecipient( $email['email']);
                //		$mail->setSender( array( $email, $name ) );
                        $mail->setSubject( 'Agora forum: New password');
                        $body = 'After user convertation from phpbb3 to agora your new password is '.$email['pswrd'];
                        $mail->setBody( $body );
                        $mail->IsHTML(true);
                        $sending = $mail->Send();
                        if (!isset($sending->code))
                        {
                            $this->setQuery('DELETE FROM #__tmp_users_psw WHERE `email`=\''.$email['email'].'\'');
                            $this->db->query();
                        }
                    }
                    $this->setQuery('INSERT INTO #__tmp_users_psw (email,pswrd) VALUES(\'last_time\', \''.time().'\')');
                    $this->db->query();
                }
                else
                {
                    $this->setQuery('DROP TABLE IF EXISTS `#__tmp_users_psw`');
                    $this->db->query();
                    $this->setQuery('DELETE FROM #__agora_config WHERE conf_name =\'o_emailpsw_perhour\'');
                    $this->db->query();
                }
            
        }
	    
	}
	*/
	
	// sync users between joomla and agora
	function sync()
	{
		$this->db->setQuery('SELECT ag.id'.
						' FROM ##__users AS ag'.
						' LEFT JOIN #__users AS jos'.
						'  ON jos.id=ag.jos_id'.
						' WHERE jos.id IS NULL');
		$deleted = $this->db->loadResultArray();
		$this->deleteList($deleted);

		// add new users to Agora
		$this->db->setQuery('SELECT jos.id, jos.lastvisitDate, jos.registerDate, jos.username, jos.email, jos.usertype'.
						' FROM ##__users AS ag'.
						' RIGHT JOIN #__users AS jos'.
						'  ON jos.id=ag.jos_id'.
						' WHERE ag.jos_id IS NULL');

		$new = $this->db->loadObjectList();

		foreach ($new as $user) {
			$this->createUserFromJoomla($user);
		}

	}
	function ajaxSearch($full_name = false)
	{
		$q 			= JRequest::getVar( 'q' );
		$searchq	=	strip_tags($q);
		if ($full_name ) {
			$where = 'j.name';
		}
		else {
			$where = 'j.username';
		}

		$query	=	' SELECT u.id, j.username, j.name FROM ##__users AS u '.
					' INNER JOIN #__users AS j '.
					' ON u.jos_id = j.id '.
					' WHERE '.$where.' LIKE '.$this->db->Quote('%'.$searchq.'%');
		$this->db->setQuery( $query );
		$users = $this->db->loadObjectList();
		return $users;
	}
	
	function hasAUP() {
		return file_exists(JPATH_SITE.DS.'components'.DS.'com_alphauserpoints'.DS.'helper.php');
	}
	
	function getAUPoints($users) {
		$result = array();
		if ($this->hasAUP() && is_array($users) && count($users) > 0) {
			$query = 'SELECT userid, points FROM #__alpha_userpoints WHERE userid IN (' . implode(',',$users) . ');';
			$this->db->setQuery($query);
			$result = $this->db->loadAssocList('userid');
		}
		return $result;
	}
}

?>
